/*
EXEC SP_LOAD_ASSEMBLY_RELEASE_LOT_WAFERIDS 123
*/
ALTER PROC SP_LOAD_ASSEMBLY_RELEASE_LOT_WAFERIDS ( @ASSEMBLY_RELEASE_ID INT )
AS
BEGIN
	DECLARE @RESULTS TABLE ( ASSEMBLY_RELEASE_ID INT, LOT_ID INT, FAB_LOT_NUMBER VARCHAR(50), LOT_NUMBER VARCHAR(50), WAFER_IDS VARCHAR(100), PART_NUMBER VARCHAR(50)  )

	DECLARE @ASSM_REL_LOTS_CURSOR CURSOR
	DECLARE @WAFER_ID_CURSOR CURSOR
	
	DECLARE @FINAL_WAFER_ID VARCHAR(100)
	DECLARE @LOT_ID INT
	DECLARE @FAB_LOT_NUMBER VARCHAR(50)
	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @LOT_WAFER_ID VARCHAR(2)

	SET @ASSM_REL_LOTS_CURSOR = CURSOR FOR SELECT DISTINCT LT.LOT_ID, LT.FAB_LOT_NUMBER, LT.LOT_NUMBER, P.PART_NUMBER FROM ASSM_RELEASE_LOT_TABLE ARLT, LOT_TABLE LT, PRODUCT P WHERE ARLT.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND ARLT.ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID
	OPEN @ASSM_REL_LOTS_CURSOR
	FETCH NEXT FROM @ASSM_REL_LOTS_CURSOR INTO @LOT_ID, @FAB_LOT_NUMBER, @LOT_NUMBER, @PART_NUMBER
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @WAFER_ID_CURSOR = CURSOR FOR SELECT DISTINCT ARLT.WAFER_ID 
		FROM ASSM_RELEASE_LOT_TABLE ARLT, SORT_BIN_RELEASE_TABLE SBRL 
		WHERE ASSEMBLY_RELEASE_ID = @ASSEMBLY_RELEASE_ID AND ARLT.LOT_ID = @LOT_ID AND ARLT.LOT_ID = SBRL.LOT_ID AND 
		ARLT.WAFER_ID = CAST(SBRL.WAFER_ID AS INT) AND ISNULL( SBRL.IS_NOTIFIED, 1) = 1
						
		SET @FINAL_WAFER_ID = ''
		OPEN @WAFER_ID_CURSOR
		FETCH NEXT FROM @WAFER_ID_CURSOR INTO @LOT_WAFER_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @FINAL_WAFER_ID = ISNULL(@FINAL_WAFER_ID,'') + @LOT_WAFER_ID + ';'
		
			SET @LOT_WAFER_ID = NULL
			FETCH NEXT FROM @WAFER_ID_CURSOR INTO @LOT_WAFER_ID
		END
		CLOSE @WAFER_ID_CURSOR
		DEALLOCATE @WAFER_ID_CURSOR

		IF ( @FINAL_WAFER_ID IS NOT NULL AND LEN( LTRIM( RTRIM( @FINAL_WAFER_ID ) ) ) > 1 )
		BEGIN
			--REMOVING ';' AT THE END
			SET @FINAL_WAFER_ID = SUBSTRING(@FINAL_WAFER_ID, 1, LEN( LTRIM( RTRIM( @FINAL_WAFER_ID ) ) ) - 1)

			INSERT INTO @RESULTS VALUES( @ASSEMBLY_RELEASE_ID, @LOT_ID, @FAB_LOT_NUMBER, @LOT_NUMBER, @FINAL_WAFER_ID, @PART_NUMBER )			
		END

		FETCH NEXT FROM @ASSM_REL_LOTS_CURSOR INTO @LOT_ID, @FAB_LOT_NUMBER, @LOT_NUMBER, @PART_NUMBER
	END
	CLOSE @ASSM_REL_LOTS_CURSOR
	DEALLOCATE @ASSM_REL_LOTS_CURSOR

	SELECT * FROM @RESULTS
END